begin tran 
;with  trouble as (

SELECT        CompanyProfileId, COUNT(ProvidesControversy) AS TotalIndicators
FROM            dbo.EventIndicators
WHERE        (ProvidesControversy = 1)
GROUP BY CompanyProfileId
Having COUNT(ProvidesControversy)  > 10
)
,  thelist as (
Select  e.id , e.CompanyProfileId, b.[ControversyIndicatorId], e.[ProvidesControversy] 
from  dbo.EventIndicators  e 
inner join [dbo].[BaseEventIndicators] b on b.[Id] = e.[BaseEventIndicatorId]
Where e.CompanyProfileId In (select CompanyProfileId from trouble)
and e.ProvidesControversy =1

)
, theones  as (
select max(id) as Id ,   CompanyProfileId, ControversyIndicatorId
from thelist e 

group by CompanyProfileId, ControversyIndicatorId
--order by e.CompanyProfileId, e.[ControversyIndicatorId] 
)
update dbo.EventIndicators   set [ProvidesControversy] = 0 
 Where dbo.EventIndicators.CompanyProfileId In (select CompanyProfileId from trouble)
  and dbo.EventIndicators.Id not in  (select id from theones)



  -- check no more trouble is left


SELECT        CompanyProfileId, COUNT(ProvidesControversy) AS TotalIndicators
FROM            dbo.EventIndicators
WHERE        (ProvidesControversy = 1)
GROUP BY CompanyProfileId
Having COUNT(ProvidesControversy)  > 10;

SELECT        TOP (100) PERCENT dbo.EventIndicatorCompanyProfiles.CompanyId, dbo.BaseEventIndicators.Name, dbo.BaseEventIndicators.Number, dbo.BaseEventIndicators.ControversyIndicatorId, 
                         dbo.EventIndicators.ProvidesControversy
FROM            dbo.EventIndicatorCompanyProfiles INNER JOIN
                         dbo.EventIndicators ON dbo.EventIndicatorCompanyProfiles.Id = dbo.EventIndicators.CompanyProfileId INNER JOIN
                         dbo.BaseEventIndicators ON dbo.EventIndicators.BaseEventIndicatorId = dbo.BaseEventIndicators.Id
WHERE        (dbo.EventIndicators.ProvidesControversy = 1) AND (dbo.EventIndicatorCompanyProfiles.CompanyId = '0bf60c41-a0e9-4b74-a132-29414bfb3b26')
ORDER BY dbo.BaseEventIndicators.Number

  rollback 
